using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.OleDb;
using System.Data;

namespace DAOLayer
{
    public class PhongDao
    {
        public static void Them(PhongDto p)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Insert into PHONG ( MaLoaiPhong,TenPhong,MaTinhTrang,GhiChu ) values(?,?,?, ?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaLoaiPhong", OleDbType.Integer);
            cmd.Parameters["@MaLoaiPhong"].Value = p.MaLoaiPhong;
            cmd.Parameters.Add("@TenPhong", OleDbType.WChar);
            cmd.Parameters["@TenPhong"].Value = p.TenPhong;
            cmd.Parameters.Add("@MaTinhTrang", OleDbType.Integer);
            cmd.Parameters["@MaTinhTrang"].Value = p.TinhTrangPhong;
            cmd.Parameters.Add("@GhiChu", OleDbType.WChar);
            cmd.Parameters["@GhiChu"].Value = p.GhiChu;
            cmd.ExecuteNonQuery();

            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            p.MaPhong = Convert.ToInt32(cmd.ExecuteScalar());
            // B5: Dong ket noi CSDL
            cn.Close();
        }
        public static List<string> LayDanhSachTenPhong()
        {
            List<string> lst = new List<string>();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select TenPhong From PHONG";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            OleDbDataReader dr = cmd.ExecuteReader();
            string dec = "";
            while (dr.Read())
            {
                dec = (string)dr["TenPhong"];
                lst.Add(dec);
            }
            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return lst;
        }
        public static DataTable LayDanhSachPhong()
        {
            DataTable dt = new DataTable();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select * From PHONG";
            //'B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            // 'B5: Dong ket noi CSDL
            cn.Close();
            return dt;

        }
        public static DataTable LayDanhSachPhongTheoMa(int ma)
        {
            DataTable dt = new DataTable();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select p.MaPhong, p.TenPhong ,lp.TenLoaiPhong ,lp.DonGiaPhong , p.GhiChu , tt.TenTinhTrang  From PHONG p ,LOAIPHONG lp,TINHTRANGPHONG tt where p.MaTinhTrang = tt.MaTinhTrang and p.MaLoaiPhong = lp.MaLoaiPhong and p.MaPhong = ?"; //+ma + "'";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaPhong", OleDbType.Integer);
            cmd.Parameters[("@MaPhong")].Value = ma;
            //'B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            // 'B5: Dong ket noi CSDL
            cn.Close();
            return dt;

        }
        public static DataTable LayToanBoPhong()
        {
            DataTable dt = new DataTable();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select p.MaPhong, p.TenPhong ,lp.TenLoaiPhong ,lp.DonGiaPhong , p.GhiChu , tt.TenTinhTrang  From PHONG p ,LOAIPHONG lp,TINHTRANGPHONG tt where p.MaTinhTrang = tt.MaTinhTrang and p.MaLoaiPhong = lp.MaLoaiPhong "; //+ma + "'";
            //'B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL,cn);
            da.Fill(dt);
            // 'B5: Dong ket noi CSDL
            cn.Close();
            return dt;

        }
        public static PhongDto LayMotPhong(int ma)
        {
            PhongDto phong = new PhongDto();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select * From PHONG Where MaPhong = ? ";
            //'B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaPhong", OleDbType.Integer);
            cmd.Parameters[("@MaPhong")].Value = ma;
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                phong.MaPhong = (int)dr["MaPhong"];
                phong.MaLoaiPhong = (int)dr["MaLoaiPhong"];
                phong.TenPhong = (string)dr["TenPhong"];
                phong.GhiChu = (string)dr["GhiChu"];
                phong.TinhTrangPhong = (int)dr["MaTinhTrang"];
            }
            cn.Close();
            return phong;

        }
        public static void Xoa(int MaPhong)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Delete From PHONG Where MaPhong = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaPhong", OleDbType.Integer);
            cmd.Parameters["@MaPhong"].Value = MaPhong;

            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            cn.Close();
        }
        public static int LayMaLoaiPhong(int dg)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select MaLoaiPhong From LOAIPHONG Where DonGia = ? ";
            //'B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@DonGia", OleDbType.Integer);
            cmd.Parameters[("@DonGia")].Value = dg;
            int ma = Convert.ToInt32(cmd.ExecuteScalar());
            cn.Close();
            return ma;
        }
        public static void CapNhatXuongCSDL(PhongDto p)
        {
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Update PHONG Set TenPhong = ? ,MaLoaiPhong = ?, MaTinhTrang = ? ,GhiChu = ?  Where MaPhong = ?  ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenPhong", OleDbType.WChar);
            cmd.Parameters["@TenPhong"].Value = p.TenPhong;
            cmd.Parameters.Add("@MaLoaiPhong", OleDbType.Integer);
            cmd.Parameters["@MaLoaiPhong"].Value = p.MaLoaiPhong;
            cmd.Parameters.Add("@MaTinhTrang", OleDbType.Integer);
            cmd.Parameters["@MaTinhTrang"].Value = p.TinhTrangPhong;
            cmd.Parameters.Add("@GhiChu", OleDbType.WChar );
            cmd.Parameters["@GhiChu"].Value = p.GhiChu;
            cmd.Parameters.Add("@MaPhong", OleDbType.Integer);
            cmd.Parameters[("@MaPhong")].Value = p.MaPhong;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public DataTable TimKiemPhong(PhongCrt p)
        {
            DataTable dt = new DataTable();
            OleDbConnection cn = Database.ConnectionData();
            OleDbCommand cmd = BuildQueryPhong(p,cn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }
        public OleDbCommand BuildQueryPhong(PhongCrt p, OleDbConnection cn)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strDKMaPhong = " 1=1 ";
            string strDKTenPhong = " 1=1 ";
            string strDKGhiChu = " 1=1 ";
            string strDKTinhTrang = " 1=1 ";
            string strDKDonGia = " 1=1 ";
            string strDKLoaiPhong = " 1=1 ";

            if (p.MaPhong != 0)
            {
                strDKMaPhong = " p.MaPhong = ?";
                cmd.Parameters.Add("@MaPhong", OleDbType.Integer);
                cmd.Parameters["@MaPhong"].Value = p.MaPhong;
            }
            if (p.TenPhong!="")
            {
                strDKTenPhong = " p.TenPhong like ?";
                cmd.Parameters.Add("@TenPhong ", OleDbType.WChar);
                cmd.Parameters["@TenPhong "].Value = ("%"
                            + (p.TenPhong + "%"));
            }
            if (p.DonGia != 0)
            {
                strDKDonGia = " lp.DonGiaPhong = ?";
                cmd.Parameters.Add("@DonGiaPhong", OleDbType.Integer);
                cmd.Parameters["@DonGiaPhong"].Value = p.DonGia;
            }
            if (p.GhiChu !="")
            {
                strDKGhiChu= " p.GhiChu like ?";
                cmd.Parameters.Add("@GhiChu", OleDbType.WChar);
                cmd.Parameters["@GhiChu"].Value = ("%"
                            + (p.GhiChu + "%"));
            }
            if (p.TinhTrang != 0)
            {
                strDKGhiChu = " p.MaTinhTrang like ?";
                cmd.Parameters.Add("@MaTinhTrang", OleDbType.Integer);
                cmd.Parameters["@MaTinhTrang"].Value = ("%"
                            + (p.TinhTrang + "%"));
            }
            if (p.TenLP!= "")
            {
                strDKLoaiPhong = " lp.TenLoaiPhong = ?";
                cmd.Parameters.Add("@TenLoaiPhong", OleDbType.WChar);
                cmd.Parameters["@TenLoaiPhong"].Value = p.TenLP;
            }
            string strDKWhere = " Where  ";
            string strSQL = "";
            if(p.DonGia==0 && p.TenLP =="" )
            {
                strDKWhere = (strDKWhere + strDKMaPhong);
                strDKWhere = (strDKWhere + (" and " + strDKTenPhong));
                strDKWhere = (strDKWhere + (" and " + strDKDonGia));
                strDKWhere = (strDKWhere + (" and " + strDKTinhTrang));
                strDKWhere = (strDKWhere + (" and " + strDKGhiChu));
                strDKWhere = (strDKWhere + (" and " + strDKLoaiPhong));
                strSQL = "Select p.MaPhong , p.TenPhong ,p.MaTinhTrang ,p.MaLoaiPhong From PHONG p ";
            }
            else if (p.MaPhong == 0 && p.TenPhong == "" && p.GhiChu == "" && p.TinhTrang == 0 )
            {
                //strDKWhere = (strDKWhere + strDKMaPhong);
                //strDKWhere = (strDKWhere + (" and " + strDKTenPhong));
                //strDKWhere = (strDKWhere + (" and " + strDKTinhTrang));
                //strDKWhere = (strDKWhere + (" and " + strDKGhiChu));
                //strDKWhere = (strDKWhere + (" and " + strDKLoaiPhong));
                if(p.DonGia!=0 && p.TenLP!="")
                {
                    strDKWhere = (strDKWhere +  strDKDonGia);
                    strDKWhere = (strDKWhere + (" and " + strDKLoaiPhong));              
                    strDKWhere = (strDKWhere + (" and lp.MaLoaiPhong = p.MaLoaiPhong"));
                    strSQL = "Select p.MaPhong , p.TenPhong , p.MaTinhTrang ,p.MaLoaiPhong  From PHONG p , LOAIPHONG lp ";
                }
                else if(p.TenLP!="" )
                {
                    strDKWhere = (strDKWhere + strDKLoaiPhong);
                    strDKWhere = (strDKWhere + (" and lp.MaLoaiPhong = p.MaLoaiPhong"));
                    strSQL = "Select p.MaPhong , p.TenPhong , p.MaTinhTrang ,p.MaLoaiPhong  From PHONG p , LOAIPHONG lp ";
                }
                else if(p.DonGia!=0)
                {

                    strDKWhere = (strDKWhere + strDKDonGia);
                    strDKWhere = (strDKWhere + (" and lp.MaLoaiPhong = p.MaLoaiPhong"));
                    strSQL = "Select p.MaPhong , p.TenPhong , p.MaTinhTrang ,p.MaLoaiPhong  From PHONG p , LOAIPHONG lp ";
                }
            }
            else
            {
                strDKWhere = (strDKWhere + strDKMaPhong);
                strDKWhere = (strDKWhere + (" and " + strDKTenPhong));
                strDKWhere = (strDKWhere + (" and " + strDKDonGia));
                strDKWhere = (strDKWhere + (" and " + strDKTinhTrang));
                strDKWhere = (strDKWhere + (" and " + strDKGhiChu));
                strDKWhere = (strDKWhere + (" and " + strDKLoaiPhong));
                strDKWhere = (strDKWhere + (" and lp.MaLoaiPhong = p.MaLoaiPhong"));
                strSQL = "Select p.MaPhong , p.TenPhong ,p.MaTinhTrang ,p.MaLoaiPhong  From PHONG p, LOAIPHONG lp ";

            }
            
            strSQL = (strSQL + strDKWhere);
            //strSQL += " Order by Ten ";
            cmd.Connection = cn;
            cmd.CommandText = strSQL;
            return cmd;
        }
    }
}
